package gentle.service.impl;/**
 * @Description:
 * @Auther: silence
 * @Date: 2019/1/4 09:28
 */

import com.baomidou.mybatisplus.plugins.Page;
import gentle.base.SqlMapper;
import gentle.base.StringUtil;
import gentle.entity.*;
import gentle.exception.DateTimeKit;
import gentle.mapper.ConsultantDao;
import gentle.service.IConsultantService;
import gentle.service.MemberProductService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.*;

/**
 * @author silence
 * @date 2019/1/4 9:28
 */
@Service
public class ConsultantServiceImpl implements IConsultantService {

    @Autowired
    private ConsultantDao dao;
    @Autowired
    private SqlMapper sqlMapper;
    @Autowired
    MemberProductService memberProductService;

    @Override
    public List<ConsultantInfo> selectPageListByQueryGetMap(Page<ConsultantInfo> page, SearchConsultantInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            if ("未设置回访".equals(searchInfo.getCustomerStatus())) {
                params.put("customerStatus", "新客首次");
                params.put("firstReturnSettings", '0');
            } else {
                params.put("customerStatus", searchInfo.getCustomerStatus());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getDiagnosisStatus())) {
            params.put("diagnosisStatus", searchInfo.getDiagnosisStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollowStatus())) {
            String followStatus = "";
            String followStatus2[] = searchInfo.getFollowStatus().split(",");
            if (followStatus2.length == 1) {
                followStatus = followStatus2[0];
            } else {
                for (int i = 0; i < followStatus2.length; i++) {
                    if (i == (followStatus2.length - 1)) {
                        followStatus += "'" + followStatus2[i];
                    } else if (i == 0) {
                        followStatus += followStatus2[i] + "'" + ",";
                    } else {
                        followStatus += "'" + followStatus2[i] + "'" + ",";
                    }
                }
            }
            params.put("followStatus", " ('" + followStatus + "')");
        }
        if (StringUtil.isNotEmpty(searchInfo.getConsumptionStatus())) {
            String consumptionStatus = "";
            String consumptionStatus2[] = searchInfo.getConsumptionStatus().split(",");
            if (consumptionStatus2.length == 1) {
                consumptionStatus = consumptionStatus2[0];
            } else {
                for (int i = 0; i < consumptionStatus2.length; i++) {
                    if (i == (consumptionStatus2.length - 1)) {
                        consumptionStatus += "'" + consumptionStatus2[i];
                    } else if (i == 0) {
                        consumptionStatus += consumptionStatus2[i] + "'" + ",";
                    } else {
                        consumptionStatus += "'" + consumptionStatus2[i] + "'" + ",";
                    }
                }
            }
            params.put("consumptionStatus", " ('" + consumptionStatus + "')");
        }
        if (StringUtil.isNotEmpty(searchInfo.getDealStatus())) {
            if ("已结账".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已结账'");
            } else if ("已开单".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已开单'");
            } else {
                params.put("dealStatus", "t.deal_status ='未开单'");
            }
        }
        String tableHead = searchInfo.getAbbreviation();
        params.put("tableName", tableHead + "customer_info");
        params.put("tableName1", tableHead + "deal_info");
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPage(page, params);
    }

    @Override
    public int getQueryPagesCount(Page<ConsultantInfo> page, SearchConsultantInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            if ("未设置回访".equals(searchInfo.getCustomerStatus())) {
                params.put("customerStatus", "新客首次");
                params.put("firstReturnSettings", '0');
            } else {
                params.put("customerStatus", searchInfo.getCustomerStatus());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getDiagnosisStatus())) {
            params.put("diagnosisStatus", searchInfo.getDiagnosisStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollowStatus())) {
            String followStatus = "";
            String followStatus2[] = searchInfo.getFollowStatus().split(",");
            if (followStatus2.length == 1) {
                followStatus = followStatus2[0];
            } else {
                for (int i = 0; i < followStatus2.length; i++) {
                    if (i == (followStatus2.length - 1)) {
                        followStatus += "'" + followStatus2[i];
                    } else if (i == 0) {
                        followStatus += followStatus2[i] + "'" + ",";
                    } else {
                        followStatus += "'" + followStatus2[i] + "'" + ",";
                    }
                }
            }
            params.put("followStatus", " ('" + followStatus + "')");
        }
        if (StringUtil.isNotEmpty(searchInfo.getConsumptionStatus())) {
            String consumptionStatus = "";
            String consumptionStatus2[] = searchInfo.getConsumptionStatus().split(",");
            if (consumptionStatus2.length == 1) {
                consumptionStatus = consumptionStatus2[0];
            } else {
                for (int i = 0; i < consumptionStatus2.length; i++) {
                    if (i == (consumptionStatus2.length - 1)) {
                        consumptionStatus += "'" + consumptionStatus2[i];
                    } else if (i == 0) {
                        consumptionStatus += consumptionStatus2[i] + "'" + ",";
                    } else {
                        consumptionStatus += "'" + consumptionStatus2[i] + "'" + ",";
                    }
                }
            }
            params.put("consumptionStatus", " ('" + consumptionStatus + "')");
        }
        if (StringUtil.isNotEmpty(searchInfo.getDealStatus())) {
            if ("已结账".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已结账'");
            } else if ("已开单".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已开单'");
            } else {
                params.put("dealStatus", "t.deal_status ='未开单'");
            }
        }
        String tableHead = searchInfo.getAbbreviation();
        params.put("tableName", tableHead + "customer_info");
        params.put("tableName1", tableHead + "deal_info");
        return dao.getQueryPagesCount(page, params);
    }

    @Override
    public List<ConsultantInfo> totalTc(String param1, String param2, String param3, String param4, String param5,
                                        String param6, String param7, String param8, String param9, String abbreviation){
        String ctable=abbreviation+"customer_info";
        String contitionInfo = " AND t.customer_status != '作废' AND t.arrival_status in (2,3,4,5)";
        if (StringUtils.isNotBlank(param1)) {
            contitionInfo += " and t.arrival_time >= '" + param1 + "' ";
        }
        if (StringUtils.isNotBlank(param2)) {
            contitionInfo += " and t.arrival_time <= '" + param2 + "' ";
        }
        if (StringUtil.isNotEmpty(param3)) {
            String followStatus = "";
            String followStatus2[] = param3.split(",");
            if (followStatus2.length == 1) {
                followStatus = followStatus2[0];
            } else {
                for (int i = 0; i < followStatus2.length; i++) {
                    if (i == (followStatus2.length - 1)) {
                        followStatus += "'" + followStatus2[i];
                    } else if (i == 0) {
                        followStatus += followStatus2[i] + "'" + ",";
                    } else {
                        followStatus += "'" + followStatus2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.follow_status in ('" + followStatus + "') ";
        }
        if (StringUtil.isNotEmpty(param4)) {
            String consumptionStatus = "";
            String consumptionStatus2[] = param4.split(",");
            if (consumptionStatus2.length == 1) {
                consumptionStatus = consumptionStatus2[0];
            } else {
                for (int i = 0; i < consumptionStatus2.length; i++) {
                    if (i == (consumptionStatus2.length - 1)) {
                        consumptionStatus += "'" + consumptionStatus2[i];
                    } else if (i == 0) {
                        consumptionStatus += consumptionStatus2[i] + "'" + ",";
                    } else {
                        consumptionStatus += "'" + consumptionStatus2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.consumption_status in ('" + consumptionStatus + "') ";
        }
        if (StringUtils.isNotBlank(param5)) {
            contitionInfo += " and t.field_tc = '" + param5 + "' ";
        }
        if (StringUtils.isNotBlank(param7)) {
            contitionInfo += " and t.deal_status = '" + param7 + "' ";
        }
        if (StringUtils.isNotBlank(param8)) {
            contitionInfo += " and t.diagnosis_status = '" + param8 + "' ";
        }
        if (StringUtils.isNotBlank(param9)) {
            contitionInfo += " and t.treatment_unit = '" + param9 + "' ";
        }
        if (StringUtils.isNotBlank(param6)) {
            contitionInfo += " and (t.customer_name like CONCAT('%','" + param6 + "','%') or t.customer_phone like CONCAT('%','" + param6 + "','%') or t.customer_phone2 like CONCAT('%','" + param6 + "','%') or " +
                    " t.wechat_number like CONCAT('%','" + param6 + "','%') or t.medical_record_number like CONCAT('%','" + param6 + "','%') or t.szm like CONCAT('%" + param6 + "%')) ";
        }
        String sql = "select count(*) as countNum from (select t.id  from " +ctable+ " t  where 1=1 "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status ) bb " +

                " union all select count(*) as countNum  from (select t.id  from " +ctable+ " t  where  t.customer_status='新客首次' "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status ) bb " +

                " union all select count(*) as countNum  from (select t.id  from " +ctable+ " t  where t.customer_status='新客多次' "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status ) bb " +

                " union all select count(*) as countNum  from (select t.id  from " +ctable+ " t  where t.customer_status='复诊' "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status ) bb " +

                " union all select count(*) as countNum  from (select t.id  from " +ctable+ " t  where  t.customer_status='老客' "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status ) bb " +

                //复诊人次
                " union all select count(*) as countNum  from (select t.id  from " +ctable+ " t  where t.customer_status !='新客首次' "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status, left(arrival_time,10) ) bb " +

                //未设置回访
                " union all select count(*) as countNum  from (select t.id  from " +ctable+ " t  where t.customer_status ='新客首次' and t.first_return_settings='0' "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status ) bb ";

        List<ConsultantInfo> tongJiInfoList = sqlMapper.selectList(sql, ConsultantInfo.class);
        return tongJiInfoList;
    }

    @Override
    public Double getTotleMoneys(String id,String abbreviation) {
        String ctable=abbreviation+"customer_info";
        Double actualAccountBalance = 0.00;
        String sql2 = "select actual_account_balance as actualAccountBalance " +
                "from " +ctable+ " t where t.reservations_id='" + id + "' limit 0,1";
        PayServicedInfo info = sqlMapper.selectOne(sql2, PayServicedInfo.class);
        if (info != null) {
            actualAccountBalance += info.getActualAccountBalance();
        }
        return actualAccountBalance;
    }

    @Override
    public CustomerInfo selectOneById(String id,String abbreviation) {
        String ctable=abbreviation+"customer_info";
        String sql = "select " +
                " (CASE t2.customer_sex  WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END ) as sex ," +
                " (CASE t2.marital_status WHEN 1 THEN '已婚' WHEN 2 THEN '未婚' WHEN 3 THEN '离异' ELSE '未知' END ) as maritalStatusName ," +
                "t2.* from " +ctable+ " t2 where id= '" + id + "';";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    @Override
    public FirstCustomerInfo firstVisitInformation(String reservationsId,String abbreviation) {
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String customerTablesd = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                customerTablesd += "select id,appointment_pay_service,arrival_time,archiving,field_tc from " + head + "customer_info t where reservations_id='" + reservationsId + "' and arrival_time is not null";
            } else {
                customerTablesd += "select id,appointment_pay_service,arrival_time,archiving,field_tc " +
                        "from " + head + "customer_info t where reservations_id='" + reservationsId + "' " +
                        "and arrival_time is not null  union ";
            }
        }
        if (abbreviationList.size() == 0) {
            return null;
        }
        FirstCustomerInfo customerInfoFirst =selectOneByReservationsIdFirst(reservationsId, customerTablesd);  //首次到店患者信息
        return customerInfoFirst;
    }

    //患者初次到店信息，如获得初诊tc
    public FirstCustomerInfo selectOneByReservationsIdFirst(String reservationsId, String customerTablesd) {
        String sql = "select id,appointment_pay_service,arrival_time,archiving,field_tc from (" + customerTablesd + ")  t order by t.arrival_time asc limit 1;";
        FirstCustomerInfo customerInfo = sqlMapper.selectOne(sql, FirstCustomerInfo.class);
        return customerInfo;
    }

    @Override
    public String  getorthodonticNumber(String reservationsId,String abbreviation,String areaAbbreviation) {
        String dtable=abbreviation+"deal_info";
        String ptable=areaAbbreviation+"pay_service_info";
        String orthodonticNumber="";
        String sql = "select t1.id as id,t1.operation_time as operationTime,t1.orthodontic_number as orthodonticNumber " +
                " from " +dtable+ " t1 left join " +ptable+ " t2 on t1.pay_service_id=t2.id " +
                " where t1.reservations_id='" + reservationsId + "' " +
                " and orthodontic_number is not null and orthodontic_number!='' and t1.deal_status='已结账' group by orthodontic_number ";
        List<PayServicedInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
        for (PayServicedInfo info : payServiceInfoList) {
            if (StringUtil.isNotEmpty(info.getOrthodonticNumber())) {
                orthodonticNumber = info.getOrthodonticNumber();
            }
        }
        return orthodonticNumber;
    }

    @Override
    public List<CustomerInfo> visitRecords(String reservationsId,String abbreviation,String outpatientName) {
        //到过的店
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String customerTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                customerTables += "select customer_name as customerName, left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,ifnull(t.remarks_reservation,'') as remarksReservation," +
                        "t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,ifnull(t.remarks,'') as remarks,t.arrival_status as arrivalStatus," +
                        "t.archiving as archiving,t.arrival_store as arrivalStore,t.customer_status as customerStatus,t.status as status," +
                        "t.appointment_pay_service as appointmentPayService " +
                        "from " + head + "customer_info t where reservations_id='" + reservationsId + "' ";
            } else {
                customerTables += "select customer_name as customerName, left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,ifnull(t.remarks_reservation,'') as remarksReservation," +
                        "t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,ifnull(t.remarks,'') as remarks,t.arrival_status as arrivalStatus," +
                        "t.archiving as archiving,t.arrival_store as arrivalStore,t.customer_status as customerStatus,t.status as status," +
                        "t.appointment_pay_service as appointmentPayService " +
                        "from " + head + "customer_info t where reservations_id='" + reservationsId + "' union ";
            }
        }
        if (abbreviationList.size() == 0) {
            return null;
        }
        //就诊记录
        String sql11 = "select * from (" + customerTables + ") t order by t.startTime desc";
        List<CustomerInfo> customerInfoList = sqlMapper.selectList(sql11, CustomerInfo.class);
        for (CustomerInfo infos : customerInfoList) {
            infos.setOutpatientName(outpatientName);
            if (infos.getStartTime() != "" && infos.getStartTime() != null) {
                infos.setStartTimeOne(infos.getStartTime().substring(0, 10));
                infos.setStartTimeTwo(infos.getStartTime().substring(11, infos.getStartTime().length()));
            }
        }
        return customerInfoList;
    }

    @Override
    public int getCustomerIsDealStatus(String customerId, String abbreviation) {
        String ctable=abbreviation+"customer_info";
        String dtable=abbreviation+"deal_info";
        String ptable=abbreviation+"recharge_info";
        String sql = "select id,isdeal_status,customer_status,deal_status  from " +ctable+ " t" +
                " where t.id='" + customerId + "' ";
        String sql1 = "select id from " + ptable+ " t where t.customer_id='" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        CustomerInfo customerInfo1 = sqlMapper.selectOne(sql1, CustomerInfo.class);
        String sql2 = "select id from " +dtable+ " t where t.customer_id='" + customerId + "' limit 1";
        CustomerInfo customerInfo2 = sqlMapper.selectOne(sql2, CustomerInfo.class);
        if (customerInfo2 != null) {
            return 1;
        } else if ("新客首次".equals(customerInfo.getCustomerStatus())) {
            return 3;
        } else if (customerInfo1 != null) {  //充了储值或积分
            return 4;
        } else {
            return 2;
        }
    }

    @Override
    public List<PayServicedInfo> payServiceSearch(String reach, String reservationsId,String treatmentUnit,String abbreviation,String areaAbbreviation){
        String ptable=areaAbbreviation+"pay_service_info";
        String sql = "";
        String str = "";
        if (StringUtil.isEmpty(treatmentUnit)) {  //没选择科室查所有科室下的收费项目
            str = "";
        } else {
            str = "and department='" + treatmentUnit + "'";
        }
        String sql2="select * from reservemoney_orthodontics_deal a where reservations_id='"+reservationsId+"' " +
                " and ('"+ DateTimeKit.now()+"' between a.recharge_time and a.end_time) limit 1";
        CustomerInfo a=sqlMapper.selectOne(sql2,CustomerInfo.class);
        String psi=" ";
        if(a==null){
            psi=" and a.first_level_project!='种植牙俱乐部'";
        }
        try {
            Double dCheckValue = Double.parseDouble(reach);
            if (dCheckValue instanceof Double == false) {
                sql = " select * from (select id as id ,department as department , first_level_project as firstLevelProject," +
                        " pay_service_name as payServiceName, pay_service_price_one as payServicePriceOne,pay_service_price_two as payServicePriceTwo " +
                        " from " + ptable + " a where " +
                        " (pay_service_name like '%" + reach + "%' or firstconcats(pay_service_name) like '%" + reach + "%') " + str + "" +psi+
                        "  and is_upper=1 order by FIND_IN_SET('" + treatmentUnit + "',department) desc) t " +
                        "  group by t.payServiceName order by FIND_IN_SET('" + treatmentUnit + "',department) desc";
            } else {
                sql = " select * from (select id as id ,department as department , first_level_project as firstLevelProject,pay_service_name as payServiceName, " +
                        " pay_service_price_one as payServicePriceOne,pay_service_price_two as payServicePriceTwo " +
                        " from " + ptable + " a where " +
                        " (pay_service_name like '%" + reach + "%' or pay_service_price_one=" + dCheckValue + " " +
                        " or pay_service_price_two=" + dCheckValue + " or firstconcats(pay_service_name) like '%" + reach + "%')  " + str + "" +psi+
                        " and is_upper=1 order by FIND_IN_SET('" + treatmentUnit + "',department) desc) t " +
                        " group by t.payServiceName order by FIND_IN_SET('" + treatmentUnit + "',department) desc";
            }
        } catch (NumberFormatException e) {
            sql = " select * from (select id as id ,department as department , first_level_project as firstLevelProject," +
                    " pay_service_name as payServiceName, pay_service_price_one as payServicePriceOne,pay_service_price_two as payServicePriceTwo " +
                    " from " + ptable + " a where " +
                    " (pay_service_name like '%" + reach + "%' or firstconcats(pay_service_name) like '%" + reach + "%') " + str + "" +psi+
                    " and is_upper=1 order by FIND_IN_SET('" + treatmentUnit + "',department) desc) t " +
                    " group by t.payServiceName order by FIND_IN_SET('" + treatmentUnit + "',department) desc";
        }
        List<PayServicedInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return payServiceInfoList;
    }

    @Override
    public List<IconInfo> getIconInfo(String cri, String abbreviation,String areaAbbreviation) {
        String ptable=areaAbbreviation+"pay_service_info";
        List<IconInfo> list = new ArrayList<>();
        String reservationsId = cri.split(",")[1];
        String iconType = cri.split(",")[2];
        String param = "";
        if (org.apache.commons.lang.StringUtils.isNotBlank(iconType) && !iconType.equals("所有类型")) {
            param += " and i.icon_name = '" + iconType + "' ";
        }
        if (org.apache.commons.lang.StringUtils.isNotBlank(reservationsId)) {
            param += " and i.reservations_id = '" + reservationsId + "' ";
        }
        try {
            String sql = "  select  i.id ,i.customer_id as customerId,i.reservations_id as reservationsId,i.icon_name as iconName," +
                    "  i.outpatient_name as outpatientName,i.diagnoseTime as diagnoseTime,i.add_name as addName,i.add_time as addTime," +
                    "i.remarks as remarks,i.image_url as imageUrl,p.pay_service_name as payServiceName  from icon_info i " +
                    "left join " + ptable+ " p on p.id=i.pay_service_id  where 1=1 " + param + " order by diagnoseTime DESC";
            list = sqlMapper.selectList(sql, IconInfo.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<CaseInfo> medicalRecord(String reservationsId, String abbreviation,String areaAbbreviation) {
        String ptable=areaAbbreviation+"pay_service_info";
        //病例记录
        //1、先查询当前客户的所有病例记录
        String caseInfoSql = "select left(t1.finnally_time,19) as finnallyTime1,t1.* from case_info t1  where t1.reservations_id='" + reservationsId + "'order by t1.finnally_time desc";
        List<CaseInfo> caseInfoList = sqlMapper.selectList(caseInfoSql, CaseInfo.class);
        for (CaseInfo infos : caseInfoList) {
            if (infos != null) {
                if (infos.getOperationTime() != "" && infos.getOperationTime() != null) {
                    infos.setOperationTime(infos.getOperationTime().substring(0, 10));
                }
                String sql3 = "select pay_service_name as payServiceName from " + ptable+ " where id ='" + infos.getPayServiceId() + "' limit 1";
                CaseInfo caseInfo1 = sqlMapper.selectOne(sql3, CaseInfo.class);
                if (caseInfo1 == null) {
                    continue;
                }
                infos.setPayServiceName(caseInfo1.getPayServiceName());
            }
        }
        //2.循环遍历每个病例记录,给对应病例的牙位属性赋值
        for (CaseInfo caseInfo : caseInfoList) {
            //然后查询牙位集合
            String toothSIteSql = "select * from tooth_site t1 where t1.case_id='" + caseInfo.getId() + "' ";
            List<ToothSite> toothSiteList = sqlMapper.selectList(toothSIteSql, ToothSite.class);
            for (ToothSite toothSite : toothSiteList) {
                //根据对应的牙位类型,给病例实体的对应牙位属性赋值
                if (toothSite.getYaType().equals("1")) {
                    caseInfo.getToothSite1List().add(toothSite);
                }
                if (toothSite.getYaType().equals("2")) {
                    caseInfo.getToothSite2List().add(toothSite);
                }
                if (toothSite.getYaType().equals("3")) {
                    caseInfo.getToothSite3List().add(toothSite);
                }
                if (toothSite.getYaType().equals("4")) {
                    caseInfo.getToothSite4List().add(toothSite);
                }
                if (toothSite.getYaType().equals("5")) {
                    caseInfo.getToothSite5List().add(toothSite);
                }
            }
        }
        return caseInfoList;
    }

    @Override
    public ArrearageRecordInfo billingRecords(String reservationsId) {
        //到过的店
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        String rechargeTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
                rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
                rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        if (abbreviationList.size() == 0) {
            return null;
        }
        //账单记录
        Double totalServiceActualPay = 0.00;
        Double totalArrears = 0.00;
        String custDealSql = "select t1.id2 as id2," +
                "t1.reservations_id as reservationsId, " +
                "t1.outpatient_name as outpatientName," +
                "left(t1.operation_time,19) as operationTime," +
                "t1.deal_status as dealStatus," +
                "GROUP_CONCAT( distinct (t1.doctor)) as doctor,GROUP_CONCAT( distinct (t1.nurse)) as nurse," +
                "t1.tc_name as tcName," +
                "sum(t1.service_actual_pay+t1.arrears+t1.deduction_amount+t1.deposit_amount) AS foldingPay," +
                "sum(t1.service_actual_pay) as serviceActualPay," +
                "sum(t1.arrears) as arrears," +
                "sum(t1.deduction_amount) as deductionAmount," +
                "sum(t1.deposit_amount) as depositAmount," +
                "t1.remarks as  remarks,(select distinct(r1.document_number) from (" + rechargeTables + ") r1 where r1.deal_id=t1.id group by r1.deal_id) as documentNumber " +
                "from (" + dealTables + ") t1 where t1.deal_status='已结账' and  t1.reservations_id = '" + reservationsId + "' " +
                "group by t1.operation_time order by t1.operation_time desc";
        List<PayServicedInfo> custDealList = sqlMapper.selectList(custDealSql, PayServicedInfo.class);
        for (int i = 0; i < custDealList.size(); i++) {
            List<PayServicedInfo> dealList = getDealInfoList(custDealList.get(i).getOperationTime(), custDealList.get(i).getReservationsId());
            custDealList.get(i).setDealList(dealList);
            custDealList.get(i).setDocumentNumber(dealList.get(0).getDocumentNumber());
            totalServiceActualPay = totalServiceActualPay + custDealList.get(i).getServiceActualPay() + custDealList.get(i).getDepositAmount();
            totalArrears = totalArrears + custDealList.get(i).getArrears();
        }
        ArrearageRecordInfo arrearageRecordInfo = new ArrearageRecordInfo();
        arrearageRecordInfo.setArrearsDealList(custDealList);
        arrearageRecordInfo.setTotalPayDealActualPay(totalServiceActualPay);
        arrearageRecordInfo.setTotalPayDealArrears(totalArrears);
        return arrearageRecordInfo;
    }

    public List<PayServicedInfo> getDealInfoList(String operationTime, String reservationsId) {
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        String rechargeTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        String sql = "select t1.id as id,t1.outpatient_name as outpatientName,left(t1.get_time,19) as getTime,t1.is_do as isDo,t1.deal_status as dealStatus," +
                "t1.doctor1 as doctor,t1.nurse1 as nurse,t1.order_discount as orderDiscount,t1.deductionAmount as deductionAmount,t1.remarks as remarks,t1.serviceActualPay as serviceActualPay," +
                "t2.recharge_people as rechargePeople,t2.checkout_method as checkoutMethod," +
                "t2.document_number as documentNumber,t1.tc_name as tcName,left(t1.operation_time,19) as operationTime from (select t.id2 as id,t.outpatient_name as outpatient_name," +
                "t.get_time as get_time,t.tc_name as tc_name,t.is_do as is_do,t.deal_status as deal_status,GROUP_CONCAT(DISTINCT t.doctor) AS doctor1,GROUP_CONCAT(DISTINCT t.nurse) AS nurse1,(sum(t.service_actual_pay)+sum(t.deposit_amount)) as serviceActualPay," +
                "t.order_discount as order_discount,sum(t.deduction_amount) as deductionAmount,t.remarks as remarks,t.operation_time as operation_time from (" + dealTables + ") t where t.operation_time='" + operationTime + "' and t.reservations_id " +
                "= '" + reservationsId + "' and t.deal_status='已结账' group by t.get_time) t1 left join " + "(select b.recharge_people as recharge_people," +
                "b.document_number as  document_number,group_concat(distinct b.checkout_method) as checkout_method, b.operation_time as operation_time from (" + rechargeTables + ") b " +
                "group by b.operation_time) t2 on t1.get_time = t2.operation_time order by t1.get_time desc";
        List<PayServicedInfo> payServiceList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return payServiceList;
    }

    @Override
    public ArrearageRecordInfo paymentRecords(String reservationsId) {
        //到过的店
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        String rechargeTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
                rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
                rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        if (abbreviationList.size() == 0) {
            return null;
        }
        //支付记录
        String payDealSql = "select t.outpatient_name as outpatientName,left(t.get_time,19) as getTime,t.is_do as isDo,t.tc_name as tcName,t.deal_status as dealStatus,t2.recharge_people " +
                "as rechargePeople,sum(t.folding_pay) as foldingPay,sum(t.service_actual_pay) as serviceActualPay, sum(t.arrears) as arrears," +
                "(sum(t.folding_pay)+sum(t.arrears)) as tkfoldingPay,sum(t.deduction_amount) AS deductionAmount,sum(t.deposit_amount) as depositAmount," +   //退款=应收+欠费
                "t2.remarks as remarks from (" + dealTables + ") t left join (select b.id2 as id2, b.recharge_people as recharge_people, " +
                "b.operation_time as operation_time,b.remarks as remarks from (" + rechargeTables + ") b group by b.operation_time) t2 on t.get_time = t2.operation_time  where " +
                "t.reservations_id='" + reservationsId + "' and t.deal_status = '已结账' group by t.get_time order by t.get_time desc;";
        List<PayServicedInfo> payDealList = sqlMapper.selectList(payDealSql, PayServicedInfo.class);
        Double totalPayDealActualPay = 0.00;
        Double totalPayDealArrears = 0.00;
        for (int i = 0; i < payDealList.size(); i++) {
            totalPayDealActualPay = BigDecimal.valueOf(totalPayDealActualPay).add(BigDecimal.valueOf(payDealList.get(i).getServiceActualPay())).doubleValue();
            totalPayDealActualPay = BigDecimal.valueOf(totalPayDealActualPay).add(BigDecimal.valueOf(payDealList.get(i).getDepositAmount())).doubleValue();

            totalPayDealArrears = BigDecimal.valueOf(totalPayDealArrears).add(BigDecimal.valueOf(payDealList.get(i).getArrears())).doubleValue();
        }
        ArrearageRecordInfo arrearageRecordInfo = new ArrearageRecordInfo();
        arrearageRecordInfo.setArrearsDealList(payDealList);
        arrearageRecordInfo.setTotalPayDealActualPay(totalPayDealActualPay);
        arrearageRecordInfo.setTotalPayDealArrears(totalPayDealArrears);
        return arrearageRecordInfo;
    }

    @Override
    public ArrearageRecordInfo arrearageRecord(String reservationsId, String abbreviation,String proportional,String areaAbbreviation) {
        String ptable=areaAbbreviation+"pay_service_info";
        //到过的店
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        if (abbreviationList.size() == 0) {
            return null;
        }
        //欠费记录
        String arrearsDealSql = "select * from (select t.id2 as id2,t.reservations_id as reservationsId,t.customer_id as customerId," +
                " t.deal_status as dealStatus,t.outpatient_name as outpatientName,t.remarks as remarks,left(t.operation_time,19) as operationTime," +
                " t.doctor as doctor,t.tc_name as tcName,t.nurse as nurse,sum(t.deposit_amount) AS depositAmount,sum(t.deduction_amount) AS deductionAmount,sum(t.folding_pay) as foldingPay," +
                " sum(t.service_actual_pay)+sum(t.deposit_amount) as serviceActualPay,sum(t.arrears) as arrears,group_concat(distinct t1.pay_service_name) as payServiceName " +
                " from (" + dealTables + ") t  left join " + ptable + " t1 on t.pay_service_id=t1.id where t.deal_status='已结账' group by t.operation_time ) t1 " +
                " where t1.reservationsId = '" + reservationsId + "' and t1.arrears  >0 order by t1.operationTime desc;";
        List<PayServicedInfo> arrearsDealList = sqlMapper.selectList(arrearsDealSql, PayServicedInfo.class);
        Double totalArrearsAmount = 0.00;
        for (int i = 0; i < arrearsDealList.size(); i++) {
            totalArrearsAmount += arrearsDealList.get(i).getArrears();
        }
        String getMoneyBalance = "select if(sum(a.actual_account_balance) is null,0,sum(a.actual_account_balance)) as moneyBalance from reservemoney_deal a " +
                " where a.reservations_id='" + reservationsId + "' limit 1";
        MembersUser membersUser1 = sqlMapper.selectOne(getMoneyBalance, MembersUser.class);
        String jfbalancesql1 = "select if(sum(score_balance) is null,0,sum(score_balance)) as scoreBalance from score_deal where reservations_id='" + reservationsId + "'";
        ScoreDealInfo scoreDealInfo1 = sqlMapper.selectOne(jfbalancesql1, ScoreDealInfo.class);
        String jfbalancesql = "select if(sum(score_balance) is null,0,sum(score_balance)) as scoreBalance from return_cash_info where reservations_id='" + reservationsId + "'";
        ScoreDealInfo scoreDealInfo = sqlMapper.selectOne(jfbalancesql, ScoreDealInfo.class);
        BigDecimal b1 = new BigDecimal(Double.parseDouble(proportional));
        BigDecimal b2 = new BigDecimal(Double.parseDouble(scoreDealInfo.getScoreBalance()));
        Double jfbalance = b1.multiply(b2).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
        ArrearageRecordInfo arrearageRecordInfo = new ArrearageRecordInfo();
        arrearageRecordInfo.setArrearsDealList(arrearsDealList);
        arrearageRecordInfo.setTotalArrearsAmount(totalArrearsAmount);
        arrearageRecordInfo.setKhmoneyBalance(membersUser1.getMoneyBalance());
        arrearageRecordInfo.setJfbalancea(scoreDealInfo1.getScoreBalance());
        arrearageRecordInfo.setKhjfbalance(jfbalance);
        return arrearageRecordInfo;
    }

    @Override
    public String getCheckoutMethodList() {
        String checkoutSql = "select id,checkout_method from checkout_method_info where type in ('1','2')"; //获取抵扣方式
        List<CheckoutMethodInfo> ckinfo=sqlMapper.selectList(checkoutSql, CheckoutMethodInfo.class);

        String checkoutMethodHtml = "";
        for (int i = 0; i < ckinfo.size(); i++) {
            checkoutMethodHtml += "<option value='" + ckinfo.get(i).getId() + "'>" +ckinfo.get(i).getCheckoutMethod() + "</option>";
        }
        return checkoutMethodHtml;
    }

    @Override
    public List<TcInfo> getTcList(String abbreviation) {
        String getListSql = " select a.id as id,a.account as tcName from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                " where b.abbreviation='" +abbreviation+ "' and c.name like CONCAT('%','tc','%') ";
        List<TcInfo> tcInfoList = sqlMapper.selectList(getListSql, TcInfo.class);
        return tcInfoList;
    }

    @Override
    public ScoreDealInfo memberInformation(String reservationsId,String abbreviation,String proportional) {
        String sql = "select (select if(sum(score_balance) is null,0,sum(score_balance)) as scoreBalance from return_cash_info where reservations_id='" + reservationsId + "') as scoreBalance," +
                "(select if(sum(score_balance) is null,0,sum(score_balance)) as scoreBalance from score_deal where reservations_id='" + reservationsId + "') as jfbalancea ," +
                "(select if(sum(a.actual_account_balance) is null,0,sum(a.actual_account_balance)) as moneyBalance from reservemoney_orthodontics_deal a  where a.reservations_id='" + reservationsId + "') as orthodonticsMoney from dual";
        ScoreDealInfo scoreDealInfo = sqlMapper.selectOne(sql, ScoreDealInfo.class);
        VipLevelInfo vinfo=memberProductService.getVipLevel(reservationsId,abbreviation);
        String vipLevelName =vinfo.getVipLevelName();
        scoreDealInfo.setVipLevelName(vipLevelName);
        String selsql = "select reservations_id,members_num from members_user where reservations_id='" + reservationsId + "' limit 1";
        MembersUser membersUser = sqlMapper.selectOne(selsql, MembersUser.class);
        String membersNum = "";
        if (membersUser != null) {
            membersNum = membersUser.getMembersNum();
        }
        scoreDealInfo.setMembersNum(membersNum);
        String getMoneyBalance = "select if(sum(a.actual_account_balance) is null,0,sum(a.actual_account_balance)) as moneyBalance from reservemoney_deal a " +
                " where a.reservations_id='" + reservationsId + "' limit 1";
        MembersUser membersUser1 = sqlMapper.selectOne(getMoneyBalance, MembersUser.class);
        scoreDealInfo.setMoneyBalance(membersUser1.getMoneyBalance());
        BigDecimal b1 = new BigDecimal(Double.parseDouble(proportional));
        BigDecimal b2 = new BigDecimal(Double.parseDouble(scoreDealInfo.getScoreBalance()));
        Double jfbalance = b1.multiply(b2).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
        scoreDealInfo.setJfbalance(jfbalance);
        return scoreDealInfo;
    }

    @Override
    public int getDealStatus(String reservationsId, String operationTime, String customerId, String abbreviation){
        String dtable=abbreviation+"deal_info";
        String sql1 = "select id from " + dtable + " " +
                " where reservations_id = '" + reservationsId + "' and operation_time='" + operationTime + "' " +
                " and customer_id = '" + customerId + "' and deal_status='已结账'";
        List<RechargeInfo> list = sqlMapper.selectList(sql1, RechargeInfo.class);
        if (list.size() > 0) {  //单已结账tc界面未刷新
            return 111;
        }
        return 1;
    }

    @Override
    public String imgDel(String id,String abbreviation,String areaAbbreviation,String userName,String userIdValue,String outpatientName){
        String ctable=abbreviation+"customer_info";
        String sql = "delete from icon_info where id = '" + id + "'";
        String selectSql2 = "select * from icon_info t where id='" + id + "' limit 1";
        IconInfo iconInfo = sqlMapper.selectOne(selectSql2, IconInfo.class);
        String customerSql = "select id,customer_name,customer_phone from " +ctable+ " where reservations_id='" + iconInfo.getReservationsId() + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(customerSql, CustomerInfo.class);
        if (customerInfo == null) {
            return "/doNoClose.html";
        }
        String inertsq1 = "INSERT INTO `icon_info_log` (createtime,reservations_id,icon_id,single_person,logtype, `userid`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,img_url) VALUES " +
                " ('" + DateTimeKit.now() + "','" + iconInfo.getReservationsId() + "','" + id + "','" + userName+ "','删除影像','" + userIdValue+ "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName+
                "','" + userName+ " 把 " + customerInfo.getCustomerName() + " 的影像在:" + DateTimeKit.now() + "进行删除','" + iconInfo.getCustomerId() + "','" + iconInfo.getImageUrl() + "');";
        sqlMapper.insert(inertsq1);//插入删除日志
        sqlMapper.delete(sql);
        return "1";
    }

    @Override
    public List<PayServicedInfo> getFirstLevelProject(String customerId,String treatmentUnit,String abbreviation,String areaAbbreviation){
        String ctable=abbreviation+"customer_info";
        String ptable=areaAbbreviation+"pay_service_info";
        String sql2 = "select id from reservemoney_orthodontics_deal a where reservations_id = (select reservations_id from "+ctable+ " " +
                "where id='" +customerId+ "') and ('" + DateTimeKit.now() + "' between a.recharge_time and a.end_time) limit 1";
        GetIdInfo getIdInfo = sqlMapper.selectOne(sql2, GetIdInfo.class);
        String psi = " ";
        if (getIdInfo == null) {
            psi = " and a.first_level_project!='种植牙俱乐部'";
        }
        String sql = "select id as id,department as department, first_level_project as firstLevelProject from " + ptable+ " a " +
                "where department= '" +treatmentUnit+ "' and is_upper = 1 " + psi + " group by first_level_project order by id";
        List<PayServicedInfo> list =sqlMapper.selectList(sql,PayServicedInfo.class);
        return list;
    }

    @Override
    public List<PayServicedInfo> getPaySeriveName(String department,String customerId,String firstLevelProject,String areaAbbreviation){
        String ptable=areaAbbreviation+"pay_service_info";
        String sql = "select id as id ,num as num ,department as department ," +
                " first_level_project as firstLevelProject,pay_service_name as payServiceName," +
                " pay_service_price_one as payServicePriceOne,pay_service_price_two as payServicePriceTwo ," +
                " unit as unit ,remarks as remarks from "+ptable+" a where department='"+department+"' " +
                " and first_level_project= '"+firstLevelProject+"' and is_upper = 1";
        List<PayServicedInfo> list =sqlMapper.selectList(sql,PayServicedInfo.class);
        return list;
    }

    @Override
    public List<PayServicedInfo> getPaySeriveNameFreeService(String vipLevelName,String areaAbbreviation){
        String ptable=areaAbbreviation+"pay_service_info";
        String pvtable=areaAbbreviation+"pay_service_vip";
        String vtable=areaAbbreviation+"vip_level_info";
        String pmtable=areaAbbreviation+"pay_service_members";
        String sql = "select a.id as id ,a.department as department ,a.first_level_project as firstLevelProject,a.pay_service_name as payServiceName, a.pay_service_price_one as payServicePriceOne,a.pay_service_price_two as payServicePriceTwo , a.unit as unit ,a.remarks as remarks ,\n" +
                "b.id as vipPayserviceId,b.num as vipPayserviceNum,ifnull(d.id,0) as membersPayserviceId  " +
                " from " + ptable + " a\n" +
                " left join " + pvtable + " b on a.id=b.pay_service_id \n" +
                " left join " + vtable + " c on b.vipid=c.id\n" +
                " left join " + pmtable + " d on b.id=d.vip_payservice_id\n" +
                " where c.vip_level_name='" + vipLevelName + "'";
        List<PayServicedInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return payServiceInfoList;
    }

    @Override
    public Double getCouponPrice(String id){
        String sql = "select coupon_price from checkout_method_info where id='" + id + "'";
        PayServiceInfo info = sqlMapper.selectOne(sql, PayServiceInfo.class);
        return info.getCouponPrice();
    }

    @Override
    public String getCustomerStatus(String customerId,String abbreviation,String userName,String roleName,Integer dealyes,Integer isRefundAdmin){
        String ctable=abbreviation+"customer_info";
        String dtable=abbreviation+"deal_info";
        String sql = "select id,customer_status as customerStatus,left(arrival_time,10) as arrivalTime,reservations_id as reservationsId,left(now(),10) as nowTime,field_tc as fieldTc " +
                " from " + ctable+ " t where t.id='" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        String sql1 = "select id from " + dtable + " t where t.reservations_id='" + customerInfo.getReservationsId() + "' " +
                "and deal_status in ('待审核','已开单') and is_do!=5 and is_do!=6";
        List<RechargeInfo> RechargeInfo = sqlMapper.selectList(sql1, RechargeInfo.class);
        int bl = 0;
        if (!(customerInfo.getNowTime()).equals(customerInfo.getArrivalTime())) {
            bl = 2;
        } else {
            if (customerInfo != null) {
                if (!(customerInfo.getFieldTc()).equals(userName)) { //此客户tc不是当前登录人
                    if ((roleName.indexOf("前台") != -1 && "待定TC".equals(customerInfo.getFieldTc())) ||
                            roleName.indexOf("管理员") != -1 || dealyes == 1||isRefundAdmin==1) {
                        bl = 0;
                    } else {
                        bl = 3;
                    }
                }
            } else {
                bl = 2;
            }
        }
        return customerInfo.getCustomerStatus() + "," + RechargeInfo.size() + "," + bl + "," + customerInfo.getFieldTc();
    }

    @Override
    public PayServiceInfo projectDetail(String id,String areaAbbreviation){
        String ptable=areaAbbreviation+"pay_service_info";
        String sql = "select * from "+ptable+" where id= '" + id + "' limit 1";
        PayServiceInfo info = sqlMapper.selectOne(sql, PayServiceInfo.class);
        return info;
    }

    @Override
    public String openBillNew(List<PaymentInfo> list){
        String ctable=list.get(0).getAbbreviation()+"customer_info";
        String ptable=list.get(0).getAreaAbbreviation()+"pay_service_info";
        String roleName=list.get(0).getRoleName();
        String customerId = list.get(0).getCustomerId();
        String upsql = "update " + ctable + " set deal_status='已开单' where id='" + customerId + "'" +
                " and arrival_status!=1 and arrival_status!=6 and arrival_status!=7 and arrival_status!=8 and arrival_status!=9 and arrival_status!=10 and arrival_time is not null" +
                " and customer_status!='复诊' ";
        sqlMapper.update(upsql);
        String sql2 = "select id,isdeal_status,customer_status,deal_status,arrival_status from " + ctable + " t" +
                " where t.id='" + customerId + "' ";
        CustomerInfo customerInfo1 = sqlMapper.selectOne(sql2, CustomerInfo.class);
        if ("1".equals(customerInfo1.getArrivalStatus()) || "6".equals(customerInfo1.getArrivalStatus()) || "9".equals(customerInfo1.getArrivalStatus()) ||
                "7".equals(customerInfo1.getArrivalStatus()) || "8".equals(customerInfo1.getArrivalStatus()) || "10".equals(customerInfo1.getArrivalStatus())) {
            return "22,''";
        }
        try {
            String selectsql = "select * from " + ctable + " where id='" + customerId + "' limit 0,1";
            CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
            if (customerInfo != null) {
                if (!"已结账".equals(customerInfo.getDealStatus())) {
                    String sql = "update " + ctable + " set deal_status='已开单' where id='" + customerId + "'";
                    sqlMapper.update(sql);
                }
            }
            if (roleName.indexOf("前台") != -1) {  //当前登录人是前台，
                for (int i = 0; i < list.size(); i++) {
                    PaymentInfo info = list.get(i);
                    String sql1 = "select * from " + ptable + " " +
                            " where id='" + info.getPayServiceId() + "' and is_teeth_project=1";
                    List<PayServicedInfo> plist = sqlMapper.selectList(sql1, PayServicedInfo.class);
                    if (plist.size() == 0) {  //开单项目中有不该前台开的单
                        return "55,''";
                    }
                }
            }
            return this.insertDealNew(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "1,''";
    }

    public String insertDealNew(List<PaymentInfo> list) {
        String outpatientName=list.get(0).getOutpatientName();
        String userName=list.get(0).getUserName();
        String levelTwoAudit=list.get(0).getLevelTwoAudit();
        String ctable=list.get(0).getAbbreviation()+"customer_info";
        String dtable=list.get(0).getAbbreviation()+"deal_info";
        String pmtable=list.get(0).getAreaAbbreviation()+"pay_service_members";
        String tptable=list.get(0).getAbbreviation()+"triage_performance";
        String tableHead =list.get(0).getAbbreviation();
        String time = DateTimeKit.now();
        VipLevelInfo vipLevelInfo = memberProductService.getVipLevel(list.get(0).getReservationsId(),list.get(0).getAbbreviation());
        String dealStatus = "已开单";
        int result = 0;
        Double wholeDiscount = list.get(0).getWholeDiscount();
        int intamount = list.get(0).getIntamount();
        int lowestPriceBill = list.get(0).getLowestPriceBill();
        double d2 = Double.valueOf(levelTwoAudit);
        if (wholeDiscount < d2 || (intamount > 0 && lowestPriceBill == 1)) {
            int discountGrade = 1;
            String discountId = UUID.randomUUID().toString();
            String discountSql = "insert into t_discount_audit (id,out_patient_name,discount,status,audit_status,customer_id,reservations_id,operation_time,apply_user," +
                    "create_time,discount_grade) values ('" + discountId + "','" + outpatientName + "'," + wholeDiscount + ",0,1,'" + list.get(0).getCustomerId() + "'," +
                    "'" + list.get(0).getReservationsId() + "','" + time + "','" + userName + "','" + time + "'," + discountGrade + ")";
            sqlMapper.insert(discountSql);
            dealStatus = "待审核";
        }
        for (int i = 0; i < list.size(); i++) {
            PaymentInfo info = list.get(i);
            String id = CommUtils.getUUID();
            String a1 = time.replace("-", "");
            String a2 = a1.replace(" ", "");
            String a3 = a2.replace(":", "");
            String a4 = tableHead.replace("_", "");
            String deductionType = info.getDeductionType();
            String deductionTypeName = "";
            String currentSql = "select id,referraler_person,referraler_time,referraler_personed,referraler_remarks from " +ctable+ " t where id='" + info.getCustomerId() + "' limit 1";
            CustomerInfo customerInfo = sqlMapper.selectOne(currentSql, CustomerInfo.class);
            String referralerPerson = "";
            String referralerPersoned = "";
            String referralerTime = "";
            String referralerRemarks = "";
            String doctor = "";
            if (StringUtil.isNotEmpty(customerInfo.getReferralerPerson())) {
                referralerPerson = "'" + customerInfo.getReferralerPerson() + "'";
            } else {
                referralerPerson = "null";
            }
            if (StringUtil.isNotEmpty(info.getDoctor())) {
                doctor = "'" + info.getDoctor() + "'";
            } else {
                doctor = "'未指定医生'";
            }
            if (StringUtil.isNotEmpty(customerInfo.getReferralerPersoned())) {
                referralerPersoned = "'" + customerInfo.getReferralerPersoned() + "'";
            } else {
                referralerPersoned = "null";
            }
            if (StringUtil.isNotEmpty(customerInfo.getReferralerTime())) {
                referralerTime = "'" + customerInfo.getReferralerTime() + "'";
            } else {
                referralerTime = null;
            }
            if (StringUtil.isNotEmpty(customerInfo.getReferralerRemarks())) {
                referralerRemarks = "'" + customerInfo.getReferralerRemarks() + "'";
            } else {
                referralerRemarks = "null";
            }
            if (deductionType != null && !deductionType.equals("") && !deductionType.equals("null")) {
                String checkoutMethodInfoSql = "select id,checkout_method from checkout_method_info where id = '" + info.getDeductionType() + "' limit 1";
                CheckoutMethodInfo checkoutMethodInfo = sqlMapper.selectOne(checkoutMethodInfoSql, CheckoutMethodInfo.class);
                if (checkoutMethodInfo != null) {
                    deductionTypeName = checkoutMethodInfo.getCheckoutMethod();
                }
            }
            if ("null".equals(info.getRemarks()) || StringUtil.isEmpty(info.getRemarks())) {
                info.setRemarks("");
            }
            //查询当前开单人是前台还是Tc
            String sql = " select a.id as id,a.account as tcName from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                    " where b.abbreviation='" + tableHead + "' and (c.name ='Tc' or c.name ='Tc主管')  and a.account='" +userName+ "' " +
                    " and b.simplename= '" +outpatientName+ "'";
            List<TcInfo> tc = sqlMapper.selectList(sql, TcInfo.class);
            String tcname =userName;
            if (tc.size() == 0) {
                tcname = "分诊TC";
            }
            if (!"0".equals(info.getVipPayserviceId())) {
                String insert = "insert into " +pmtable+ " (pay_service_id,vip_payservice_id,reservations_id,operation_time) " +
                        "values ('" + info.getPayServiceId() + "','" + info.getVipPayserviceId() + "','" + info.getReservationsId() + "','" + time + "')";
                sqlMapper.insert(insert);
            }
            String insertSql = "insert into " + dtable + " (id,customer_id,reservations_id,pay_service_id,tc_name,referraler,service_actual_pay,remarks,deal_status,operation_time," +
                    "get_time,outpatient_name,folding_pay,pay_service,arrears,left_up,left_down,right_up,right_down,doctor,nurse,dental_hygienist,type_number," +
                    "id2,order_discount,pay_service_num,pay_service_numOld," +
                    "deduction_type,deduction_typeName,deduction_amount,deposit_amount,primary_amount,discount_hide,whole_discount,referraler_person,referraler_personed,referraler_time,referraler_remarks,no_deal_cause) " +
                    "values ('" + a4 + id + a3 + "','" + info.getCustomerId() + "','" + info.getReservationsId() + "','" + info.getPayServiceId() + "','" + tcname + "','',"
                    + info.getServiceActualPay() + ",'" + info.getRemarks() + "','" + dealStatus + "','" + time + "','" + time + "','" + outpatientName+ "'," + info.getFoldingPay() + "," + info.getPayService() +
                    "," + info.getArrears() + ",'" + info.getLeftUp() + "','" + info.getLeftDown() + "','" + info.getRightUp() + "','" + info.getRightDown() + "'," + doctor + ",'" + info.getNurse() + "','" + info.getDentalHygienist() + "','','" + a4 + id + a3 + "'," +
                    info.getOrderDiscount() + "," + info.getPayServiceNum() + "," + info.getPayServiceNum() + ",'" + info.getDeductionType() + "','" + deductionTypeName + "'," + info.getDeductionAmount() + "," +
                    "" + info.getDepositAmount() + "," + info.getPrimaryAmount() + "," + info.getDiscountHide() + "," + info.getWholeDiscount() + "," + referralerPerson + "," + referralerPersoned + "," + referralerTime + "," + referralerRemarks + ",'" + info.getReservationsIdx() + "')";
            String insertSql1 = "insert into " + tptable + " (id,customer_id,reservations_id,pay_service_id,tc_name,referraler,service_actual_pay,remarks,deal_status,operation_time," +
                    "get_time,outpatient_name,folding_pay,pay_service,arrears,left_up,left_down,right_up,right_down,doctor,nurse,dental_hygienist,type_number," +
                    "id2,order_discount,pay_service_num,pay_service_numOld," +
                    "deduction_type,deduction_typeName,deduction_amount,deposit_amount,primary_amount,discount_hide,whole_discount,no_deal_cause) " +
                    "values ('" + a4 + id + a3 + "','" + info.getCustomerId() + "','" + info.getReservationsId() + "','" + info.getPayServiceId() + "','" + tcname + "','',"
                    + info.getServiceActualPay() + ",'" + info.getRemarks() + "','" + dealStatus + "','" + time + "','" + time + "','" + outpatientName + "'," + info.getFoldingPay() + "," + info.getPayService() +
                    "," + info.getArrears() + ",'" + info.getLeftUp() + "','" + info.getLeftDown() + "','" + info.getRightUp() + "','" + info.getRightDown() + "'," + doctor + ",'" + info.getNurse() + "','" + info.getDentalHygienist() + "','','" + a4 + id + a3 + "'," +
                    info.getOrderDiscount() + "," + info.getPayServiceNum() + "," + info.getPayServiceNum() + ",'" + info.getDeductionType() + "','" + deductionTypeName + "'," + info.getDeductionAmount() + "," +
                    "" + info.getDepositAmount() + "," + info.getPrimaryAmount() + "," + info.getDiscountHide() + "," + info.getWholeDiscount() + ",'" + info.getReservationsIdx() + "')";
            result += sqlMapper.insert(insertSql);
            sqlMapper.insert(insertSql1);
        }
        return result + "," + time;
    }

    @Override
    public String updateDealInfo(List<PaymentInfo> list){
        String ptable=list.get(0).getAreaAbbreviation()+"pay_service_info";
        String roleName=list.get(0).getRoleName();
        String result = "";
        try {
            String dealStatus = this.selectDealStatus(list.get(0).getReservationsId(), list.get(0).getOperationTime(), list.get(0).getAbbreviation());
            if ("已开单".equals(dealStatus) || "待审核".equals(dealStatus)) {
                if (roleName.indexOf("前台") != -1) {  //当前登录人是前台，
                    for (int i = 0; i < list.size(); i++) {
                        PaymentInfo info = list.get(i);
                        String sql1 = "select * from " + ptable+ " " +
                                " where id='" + info.getPayServiceId() + "' and is_teeth_project=1";
                        List<PayServicedInfo> plist = sqlMapper.selectList(sql1, PayServicedInfo.class);
                        if (plist.size() == 0) {  //开单项目中有不该前台开的单
                            return "55,''";
                        }
                    }
                }
                this.deleteDealNew(list.get(0).getReservationsId(), list.get(0).getOperationTime(), list.get(0).getAbbreviation(),list.get(0).getAreaAbbreviation());
                result = this.insertDealNew(list);
            } else if ("已结账".equals(dealStatus)) {
                result = "-2,''";
            } else {
                result = "-3,''";
            }
        } catch (Exception e) {
            e.printStackTrace();
            result = "-1,''";
        }
        return result;
    }

    public String selectDealStatus(String reservationsId, String operationTime,String abbreviation) {
        String dtable=abbreviation+"deal_info";
        String sql = "select id,deal_status from " +dtable+ " where reservations_id = '" + reservationsId + "' and operation_time = '" + operationTime + "'";
        List<PayServicedInfo> list = sqlMapper.selectList(sql, PayServicedInfo.class);
        String status = null;
        if (list.size() > 0) {
            status = list.get(0).getDealStatus();
        }
        return status;
    }

    public int deleteDealNew(String reservationsId, String operationTime,String abbreviation,String areaAbbreviation) {
        String dtable=abbreviation+"deal_info";
        String tptable=abbreviation+"triage_performance";
        String pmtable=areaAbbreviation+"pay_service_members";
        String discountSql = "select id from t_discount_audit where reservations_id='" + reservationsId + "' and operation_time='" + operationTime + "'";
        List<DiscountAudit> list = sqlMapper.selectList(discountSql, DiscountAudit.class);
        if (list.size() > 0) {
            String delDiscount = "delete from t_discount_audit where reservations_id='" + reservationsId + "' and operation_time='" + operationTime + "'";
            sqlMapper.delete(delDiscount);
        }
        String sql = "delete from " + dtable + " where reservations_id='" + reservationsId + "' and operation_time='" + operationTime + "'";
        String sql1 = "delete from " + tptable + " where reservations_id='" + reservationsId + "' and operation_time='" + operationTime + "'";
        sqlMapper.delete(sql1);
        String sql2 = "delete from " + pmtable+ " where reservations_id='" + reservationsId + "' and operation_time='" + operationTime + "'";
        sqlMapper.delete(sql2);
        return sqlMapper.delete(sql);
    }

    @Override
    public int add_diagnosisContent1(String id, String reservationsId, String diagnosisContent, String abbreviation, String userName, String outpatientName){
        String ctable=abbreviation+"customer_info";
        String sql = "insert into  `tc_diagnosis_info` (`customer_id`,`reservations_id`,`tc_name`,`diagnosis_content`,`diagnosis_time`,outpatient_name) VALUES" +
                "('" + id + "','" + reservationsId + "','" + userName + "','"
                + diagnosisContent + "','" + DateTimeKit.now() + "','" + outpatientName + "');";
        sqlMapper.insert(sql);
        String sql2 = "update " + ctable+ " set diagnosis_status='已看诊' where reservations_id='" + reservationsId + "'";
        return sqlMapper.update(sql2);
    }

    @Override
    public int repairArrears(String operationTimes, Double arrearsAmount, String reservationsId, String arrearsRemark, String tcName, String customerId, String arrearsOutpatientName,String abbreviations,String outpatientName) {
        String[] operationTime = operationTimes.split(",");
        String dtable =abbreviations+"deal_info";
        String tptable =abbreviations+"triage_performance";
        String abbreviation = "select id,abbreviation from dept where simplename='" + arrearsOutpatientName + "'";
        Dept dept = sqlMapper.selectOne(abbreviation, Dept.class);
        int result = 0;
        for (int i = 0; i < operationTime.length; i++) {
            String operationTimeStr = operationTime[i];
            String dealSql = "select t.id2 as id2, t.customer_id as customerId,t.reservations_id,t.pay_service_id as payServiceId,t.primary_amount as primaryAmount," +
                    "t.tc_name as tcName,sum(t.arrears) as arrears,t.operation_time as operationTime,t.outpatient_name as outpatientName,t.folding_pay as foldingPay," +
                    "t.pay_service as payService,t.left_up as leftUp,t.left_down as leftDown,t.right_up as rightUp,t.right_down as rightDown,t.doctor as doctor,t.nurse as nurse," +
                    "t.order_discount as orderDiscount,t.pay_service_num as payServiceNum,t.pay_service_numold as payServiceNumold,t.deduction_type as deductionType,t.deduction_typeName as deductionTypeName," +
                    "t.deduction_amount as deductionAmount,t.deposit_amount as depositAmount,sum(t.service_actual_pay) as serviceActualPay,t.discount_hide as " +
                    "discountHide,t.whole_discount as wholeDiscount,t.referraler_person as referralerPerson,t.referraler_personed as referralerPersoned,t.referraler_remarks as referralerRemarks,t.referraler_time as referralerTime  " +
                    "from " + dept.getAbbreviation() + "deal_info t where t.reservations_id = '" + reservationsId + "' and t.operation_time = '" + operationTimeStr + "' group by t.id2";
            List<PayServicedInfo> dealList = sqlMapper.selectList(dealSql, PayServicedInfo.class);
            String time = DateTimeKit.now();
            String a1 = time.replace("-", "");
            String a2 = a1.replace(" ", "");
            String a3 = a2.replace(":", "");
            String a4 = abbreviations.replace("_", "");
            String referralerPerson = "";
            String referralerPersoned = "";
            String referralerTime = "";
            String referralerRemarks = "";
            for (int j = 0; j < dealList.size(); j++) {
                String id = CommUtils.getUUID();
                PayServicedInfo dealInfo = dealList.get(j);
                if (dealInfo.getReferralerPerson() == null || dealInfo.getReferralerPerson() == "") {
                    referralerPerson = "null";
                } else {
                    referralerPerson = "'" + dealInfo.getReferralerPerson() + "'";
                }
                if (dealInfo.getReferralerPersoned() == null || dealInfo.getReferralerPersoned() == "") {
                    referralerPersoned = "null";
                } else {
                    referralerPersoned = "'" + dealInfo.getReferralerPersoned() + "'";
                }
                if (dealInfo.getReferralerTime() == null || dealInfo.getReferralerTime() == "") {
                    referralerTime = "null";
                } else {
                    referralerTime = "'" + dealInfo.getReferralerTime() + "'";
                }
                if (dealInfo.getReferralerRemarks() == null || dealInfo.getReferralerRemarks() == "") {
                    referralerRemarks = "null";
                } else {
                    referralerRemarks = "'" + dealInfo.getReferralerRemarks() + "'";
                }
                if (arrearsAmount >= dealInfo.getArrears()) {
                    Double arrearsf = 0 - dealInfo.getArrears();
                    String insertSql = "insert into " + dtable + " (id,customer_id,reservations_id,is_do,pay_service_id,tc_name,service_actual_pay," +
                            "remarks,deal_status,operation_time,get_time,outpatient_name,folding_pay,pay_service,arrears,left_up,left_down,right_up," +
                            "right_down,doctor,nurse,id2,order_discount,pay_service_num,pay_service_numold,deduction_type,deduction_typeName," +
                            "deduction_amount,deposit_amount,whole_discount," +
                            "discount_hide,primary_amount,referraler_person,referraler_personed,referraler_time,referraler_remarks) values (" +
                            "'" + a4 + id + a3 + "','" + customerId + "','" + dealInfo.getReservationsId() + "','3'," + dealInfo.getPayServiceId() + "," +
                            "'" + tcName + "','" + dealInfo.getArrears() + "','" + arrearsRemark + "','已开单','" + dealInfo.getOperationTime() + "'," +
                            "'" + time + "','" + outpatientName + "',0,'" + dealInfo.getPayService() + "','" + arrearsf +
                            "','" + dealInfo.getLeftUp() + "','" + dealInfo.getLeftDown() + "','" + dealInfo.getRightUp() + "','" + dealInfo.getRightDown() + "'," +
                            "'" + dealInfo.getDoctor() + "','" + dealInfo.getNurse() + "','" + dealInfo.getId2() + "','" + dealInfo.getOrderDiscount() + "','" + dealInfo.getPayServiceNum() + "','" + dealInfo.getPayServiceNumOld() + "'," +
                            "'" + dealInfo.getDeductionType() + "','" + dealInfo.getDeductionTypeName() + "',0.00,0.00,'" + dealInfo.getWholeDiscount() + "',0,0," + referralerPerson + "," + referralerPersoned + "," + referralerTime + "," + referralerRemarks + ")";
                    String insertSql1 = "insert into " + tptable + " (id,customer_id,reservations_id,is_do,pay_service_id,tc_name,service_actual_pay," +
                            "remarks,deal_status,operation_time,get_time,outpatient_name,folding_pay,pay_service,arrears,left_up,left_down,right_up," +
                            "right_down,doctor,nurse,id2,order_discount,pay_service_num,pay_service_numold,deduction_type,deduction_typeName," +
                            "deduction_amount,deposit_amount,whole_discount," +
                            "discount_hide,primary_amount) values (" +
                            "'" + a4 + id + a3 + "','" + customerId + "','" + dealInfo.getReservationsId() + "','3'," + dealInfo.getPayServiceId() + "," +
                            "'" + tcName + "','" + dealInfo.getArrears() + "','" + arrearsRemark + "','已开单','" + dealInfo.getOperationTime() + "'," +
                            "'" + time + "','" + outpatientName + "',0,'" + dealInfo.getPayService() + "','" + arrearsf +
                            "','" + dealInfo.getLeftUp() + "','" + dealInfo.getLeftDown() + "','" + dealInfo.getRightUp() + "','" + dealInfo.getRightDown() + "'," +
                            "'" + dealInfo.getDoctor() + "','" + dealInfo.getNurse() + "','" + dealInfo.getId2() + "','" + dealInfo.getOrderDiscount() + "','" + dealInfo.getPayServiceNum() + "','" + dealInfo.getPayServiceNumOld() + "'," +
                            "'" + dealInfo.getDeductionType() + "','" + dealInfo.getDeductionTypeName() + "',0.00,0.00,'" + dealInfo.getWholeDiscount() + "',0,0)";
                    result += sqlMapper.insert(insertSql);
                    result += sqlMapper.insert(insertSql1);
                    arrearsAmount = arrearsAmount - dealInfo.getArrears();
                } else {
                    String insertSql = "insert into " + dtable + " (id,customer_id,reservations_id,is_do,pay_service_id,tc_name,service_actual_pay," +
                            "remarks,deal_status,operation_time,get_time,outpatient_name,folding_pay,pay_service,arrears,left_up,left_down,right_up," +
                            "right_down,doctor,nurse,id2,order_discount,pay_service_num,pay_service_numold,deduction_type,deduction_typeName," +
                            "deduction_amount,deposit_amount,whole_discount," +
                            "discount_hide,primary_amount,referraler_person,referraler_personed,referraler_time,referraler_remarks)  values (" +
                            "'" + a4 + id + a3 + "','" + customerId + "','" + dealInfo.getReservationsId() + "','3'," + dealInfo.getPayServiceId() + "," +
                            "'" + tcName + "'," + arrearsAmount + ",'" + arrearsRemark + "','已开单','" + dealInfo.getOperationTime() + "'," +
                            "'" + time + "','" + outpatientName + "',0," + dealInfo.getPayService() + ",-" + arrearsAmount +
                            ",'" + dealInfo.getLeftUp() + "','" + dealInfo.getLeftDown() + "','" + dealInfo.getRightUp() + "','" + dealInfo.getRightDown() + "'," +
                            "'" + dealInfo.getDoctor() + "','" + dealInfo.getNurse() + "','" + dealInfo.getId2() + "'," + dealInfo.getOrderDiscount() + "," + dealInfo.getPayServiceNum() + "," + dealInfo.getPayServiceNumOld() + "," +
                            "'" + dealInfo.getDeductionType() + "','" + dealInfo.getDeductionTypeName() + "',0.00,0.00," + dealInfo.getWholeDiscount() + ",0,0," + referralerPerson + "," + referralerPersoned + "," + referralerTime + "," + referralerRemarks + ")";
                    String insertSql1 = "insert into " + tptable + " (id,customer_id,reservations_id,is_do,pay_service_id,tc_name,service_actual_pay," +
                            "remarks,deal_status,operation_time,get_time,outpatient_name,folding_pay,pay_service,arrears,left_up,left_down,right_up," +
                            "right_down,doctor,nurse,id2,order_discount,pay_service_num,pay_service_numold,deduction_type,deduction_typeName," +
                            "deduction_amount,deposit_amount,whole_discount," +
                            "discount_hide,primary_amount)  values (" +
                            "'" + a4 + id + a3 + "','" + customerId + "','" + dealInfo.getReservationsId() + "','3'," + dealInfo.getPayServiceId() + "," +
                            "'" + tcName + "'," + arrearsAmount + ",'" + arrearsRemark + "','已开单','" + dealInfo.getOperationTime() + "'," +
                            "'" + time + "','" + outpatientName + "',0," + dealInfo.getPayService() + ",-" + arrearsAmount +
                            ",'" + dealInfo.getLeftUp() + "','" + dealInfo.getLeftDown() + "','" + dealInfo.getRightUp() + "','" + dealInfo.getRightDown() + "'," +
                            "'" + dealInfo.getDoctor() + "','" + dealInfo.getNurse() + "','" + dealInfo.getId2() + "'," + dealInfo.getOrderDiscount() + "," + dealInfo.getPayServiceNum() + "," + dealInfo.getPayServiceNumOld() + "," +
                            "'" + dealInfo.getDeductionType() + "','" + dealInfo.getDeductionTypeName() + "',0.00,0.00," + dealInfo.getWholeDiscount() + ",0,0)";
                    result += sqlMapper.insert(insertSql);
                    result += sqlMapper.insert(insertSql1);
                    arrearsAmount = 0.00;
                }
            }
        }
        return result;
    }

    @Override
    public int repairArrearsYh(List<PaymentInfo> list){
        String outpatientName=list.get(0).getOutpatientName();
        String dtable=list.get(0).getAbbreviation()+"deal_info";
        String tptable=list.get(0).getAbbreviation()+"triage_performance";
        int result = 0;
        String time = DateTimeKit.now();
        Double arrearsAmount = list.get(0).getArrearsAmount();
        for (int i = 0; i < list.size(); i++) {
            PaymentInfo paymentInfo = list.get(i);
            String dealSql = "select t.id2 as id2, t.customer_id as customerId,t.reservations_id,t.pay_service_id as payServiceId,t.primary_amount as primaryAmount," +
                    "t.tc_name as tcName,sum(t.arrears) as arrears,t.operation_time as operationTime,t.outpatient_name as outpatientName,t.folding_pay as foldingPay," +
                    "t.pay_service as payService,t.left_up as leftUp,t.left_down as leftDown,t.right_up as rightUp,t.right_down as rightDown,t.doctor as doctor,t.nurse as nurse," +
                    "t.order_discount as orderDiscount,t.pay_service_num as payServiceNum,t.pay_service_numold as payServiceNumold,t.deduction_type as deductionType,t.deduction_typeName as deductionTypeName," +
                    "t.deduction_amount as deductionAmount,t.deposit_amount as depositAmount,sum(t.service_actual_pay) as serviceActualPay,t.discount_hide as " +
                    "discountHide,t.whole_discount as wholeDiscount,t.referraler_person as referralerPerson,t.referraler_personed as referralerPersoned,t.referraler_remarks as referralerRemarks,t.referraler_time as referralerTime  " +
                    "from " + dtable + " t where t.reservations_id = '" + paymentInfo.getReservationsId() + "' " +
                    "and t.get_time = '" + paymentInfo.getOperationTime() + "' and id2='" + paymentInfo.getId2() + "' limit 1";
            PayServiceInfo dealInfo = sqlMapper.selectOne(dealSql, PayServiceInfo.class);
            String a1 = time.replace("-", "");
            String a2 = a1.replace(" ", "");
            String a3 = a2.replace(":", "");
            String a4 = list.get(0).getAbbreviation().replace("_", "");
            String referralerPerson = "";
            String referralerPersoned = "";
            String referralerTime = "";
            String referralerRemarks = "";
            String id = CommUtils.getUUID();
            if (dealInfo.getReferralerPerson() == null || dealInfo.getReferralerPerson() == "") {
                referralerPerson = "null";
            } else {
                referralerPerson = "'" + dealInfo.getReferralerPerson() + "'";
            }
            if (dealInfo.getReferralerPersoned() == null || dealInfo.getReferralerPersoned() == "") {
                referralerPersoned = "null";
            } else {
                referralerPersoned = "'" + dealInfo.getReferralerPersoned() + "'";
            }
            if (dealInfo.getReferralerTime() == null || dealInfo.getReferralerTime() == "") {
                referralerTime = "null";
            } else {
                referralerTime = "'" + dealInfo.getReferralerTime() + "'";
            }
            if (dealInfo.getReferralerRemarks() == null || dealInfo.getReferralerRemarks() == "") {
                referralerRemarks = "null";
            } else {
                referralerRemarks = "'" + dealInfo.getReferralerRemarks() + "'";
            }
            String deductionTypeId = "";
            String deductionTypeName = "";
            if (paymentInfo.getDeductionType2() == null || paymentInfo.getDeductionType2() == "") {
                deductionTypeId = "''";
                deductionTypeName = "''";
            } else {
                String sql1 = "select * from checkout_method_info a where id='" + paymentInfo.getDeductionType2() + "' limit 1";
                CheckoutMethodInfo checkoutMethodInfo = sqlMapper.selectOne(sql1, CheckoutMethodInfo.class);
                deductionTypeName = "'" + checkoutMethodInfo.getCheckoutMethod() + "'";
                deductionTypeId = "'" + paymentInfo.getDeductionType2() + "'";
            }
            Double deductionAmount = 0.00;
            if (paymentInfo.getDeductionAmount2() == null || paymentInfo.getDeductionAmount2().toString() == "") {
                deductionAmount = 0.00;
            } else {
                deductionAmount = paymentInfo.getDeductionAmount2();
            }
            Double depositAmount = 0.00;
            if (paymentInfo.getDepositAmount2() == null || paymentInfo.getDepositAmount2().toString() == "") {
                depositAmount = 0.00;
            } else {
                depositAmount = paymentInfo.getDepositAmount2();
            }
            Double serviceActualPay = 0.00;
            Double arrears = 0.00;
            if (dealInfo.getArrears() == (deductionAmount + depositAmount)) {   //当前的欠费 等于 抵扣金额
                serviceActualPay = 0.00;   //实收0
                arrears = -(deductionAmount + depositAmount);  //欠费就是储值抵扣加返现抵扣
            }
            if (dealInfo.getArrears() > (deductionAmount + depositAmount)) {    //当前的欠费 大于 抵扣金额
                if (arrearsAmount >= (dealInfo.getArrears() - (deductionAmount + depositAmount))) {   //真实的欠费钱 大于等于 当前的欠费 减 抵扣金额
                    serviceActualPay = dealInfo.getArrears() - (deductionAmount + depositAmount);
                    arrears = -dealInfo.getArrears();
                    arrearsAmount = arrearsAmount - (dealInfo.getArrears() - (deductionAmount + depositAmount));
                    list.get(0).setArrearsAmount(arrearsAmount);    //真实的欠费钱 扣去本个项目收的钱
                } else if (arrearsAmount < (dealInfo.getArrears() - (deductionAmount + depositAmount))) {   //真实的欠费钱 小 当前的欠费 减 抵扣金额
                    serviceActualPay = arrearsAmount;
                    arrears = -(arrearsAmount + deductionAmount + depositAmount);
                    arrearsAmount = 0.00;
                    list.get(0).setArrearsAmount(arrearsAmount);
                } else {
                    serviceActualPay = 0.00;
                    arrears = -(deductionAmount + depositAmount);
                }
            }
            String insertSql = "insert into " + dtable + " (id,customer_id,reservations_id,is_do,pay_service_id,tc_name,service_actual_pay," +
                    "remarks,deal_status,operation_time,get_time,outpatient_name,folding_pay,pay_service,arrears,left_up,left_down,right_up," +
                    "right_down,doctor,nurse,id2,order_discount,pay_service_num,pay_service_numold,deduction_type,deduction_typeName," +
                    "deduction_amount,deposit_amount,whole_discount," +
                    "discount_hide,primary_amount,referraler_person,referraler_personed,referraler_time,referraler_remarks)  values (" +
                    "'" + a4 + id + a3 + "','" + paymentInfo.getCustomerId() + "','" + dealInfo.getReservationsId() + "','3'," + dealInfo.getPayServiceId() + "," +
                    "'" + paymentInfo.getTcName() + "'," + serviceActualPay + ",'" + paymentInfo.getArrearsRemarks() + "','已开单','" + dealInfo.getOperationTime() + "'," +
                    "'" + time + "','" + outpatientName + "',0," + dealInfo.getPayService() + "," + arrears +
                    ",'" + dealInfo.getLeftUp() + "','" + dealInfo.getLeftDown() + "','" + dealInfo.getRightUp() + "','" + dealInfo.getRightDown() + "'," +
                    "'" + dealInfo.getDoctor() + "','" + dealInfo.getNurse() + "','" + dealInfo.getId2() + "'," + dealInfo.getOrderDiscount() + "," + dealInfo.getPayServiceNum() + "," + dealInfo.getPayServiceNumOld() + "," +
                    "" + deductionTypeId + "," + deductionTypeName + "," + deductionAmount + "," + depositAmount + "," + dealInfo.getWholeDiscount() + ",0,0," + referralerPerson + "," + referralerPersoned + "," + referralerTime + "," + referralerRemarks + ")";
            String insertSql1 = "insert into " + tptable + " (id,customer_id,reservations_id,is_do,pay_service_id,tc_name,service_actual_pay," +
                    "remarks,deal_status,operation_time,get_time,outpatient_name,folding_pay,pay_service,arrears,left_up,left_down,right_up," +
                    "right_down,doctor,nurse,id2,order_discount,pay_service_num,pay_service_numold,deduction_type,deduction_typeName," +
                    "deduction_amount,deposit_amount,whole_discount," +
                    "discount_hide,primary_amount)  values (" +
                    "'" + a4 + id + a3 + "','" + paymentInfo.getCustomerId() + "','" + dealInfo.getReservationsId() + "','3'," + dealInfo.getPayServiceId() + "," +
                    "'" + paymentInfo.getTcName() + "'," + serviceActualPay + ",'" + paymentInfo.getArrearsRemarks() + "','已开单','" + dealInfo.getOperationTime() + "'," +
                    "'" + time + "','" + outpatientName + "',0," + dealInfo.getPayService() + "," + arrears +
                    ",'" + dealInfo.getLeftUp() + "','" + dealInfo.getLeftDown() + "','" + dealInfo.getRightUp() + "','" + dealInfo.getRightDown() + "'," +
                    "'" + dealInfo.getDoctor() + "','" + dealInfo.getNurse() + "','" + dealInfo.getId2() + "'," + dealInfo.getOrderDiscount() + "," + dealInfo.getPayServiceNum() + "," + dealInfo.getPayServiceNumOld() + "," +
                    "" + deductionTypeId + "," + deductionTypeName + "," + deductionAmount + "," + depositAmount + "," + dealInfo.getWholeDiscount() + ",0,0)";
            result += sqlMapper.insert(insertSql);
            result += sqlMapper.insert(insertSql1);
        }
        return result;
    }
}
